package edu.yctc.manage.service.impl;

import edu.yctc.manage.dao.CourseDAO;
import edu.yctc.manage.dao.ScoreDAO;
import edu.yctc.manage.dao.SdeptDAO;
import edu.yctc.manage.dao.StudentDAO;
import edu.yctc.manage.entity.CourseDO;
import edu.yctc.manage.entity.ScoreDO;
import edu.yctc.manage.entity.SdeptDO;
import edu.yctc.manage.entity.StudentDO;
import edu.yctc.manage.service.ImportService;
import edu.yctc.manage.utils.ExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.InputStream;

/**
 * @ClassName ImportServiceImpl
 * @Description TODO
 * @Author zyj
 * @Date 2021/3/31 19:07
 * @Version 1.0
 **/
@Service
public class ImportServiceImpl implements ImportService {

    private final static String EXCEL_XLS = "xls"; // Excel 2003

    private final static String EXCEL_XLSX = "xlsx"; // Excel 2007/2010

    private final static int FIRST_SHEET = 0;

    // 默认性别-男
    private final static int DEFAULT_GENDER = 1;

    // 默认院系-计算机与信息技术学院
    private final static long DEFAULT_SDEPT_ID = 1L;

    private final static Logger LOG = LoggerFactory.getLogger("serviceLogger");

    @Resource
    private StudentDAO studentDAO;

    @Resource
    private CourseDAO courseDAO;

    @Resource
    private SdeptDAO sdeptDAO;

    @Resource
    private ScoreDAO scoreDAO;

    @Override public void ImportScore(MultipartFile multipartFile) {

        try {
            // 解析excel
            String fileName = multipartFile.getOriginalFilename();// 获取文件名
            InputStream inputStream = multipartFile.getInputStream();// 输入流
            Workbook workbook = null;
            // excel 2003
            if (fileName.endsWith(EXCEL_XLS)) {
                workbook = new HSSFWorkbook(inputStream);
            }
            // excel 2007/2010
            else if (fileName.endsWith(EXCEL_XLSX)) {
                workbook = new XSSFWorkbook(inputStream);
            }
            Sheet sheet = workbook.getSheetAt(FIRST_SHEET);
            int rowsSummary = sheet.getLastRowNum();// 获得总行数,返回的是总行数-1
            // 遍历excel表
            for (int i = 1; i < rowsSummary + 1; i++) {
                Row row = sheet.getRow(i);

                if (row != null) {
                    ScoreDO scoreDO = new ScoreDO();
                    // 姓名
                    String studentName = ExcelUtil.getCellValue(row.getCell(0)).toString();
                    // 课程名
                    String courseName = ExcelUtil.getCellValue(row.getCell(1)).toString();
                    // 开设院系
                    String sdeptName = ExcelUtil.getCellValue(row.getCell(2)).toString();
                    // 分数
                    String score = ExcelUtil.getCellValue(row.getCell(3)).toString();
                    // 查询有无此学生
                    StudentDO studentDO = studentDAO.getStudentDOByName(studentName);
                    if (studentDO == null) {
                        // 新增学生信息
                        // 信息默认
                        studentDO = new StudentDO();
                        studentDO.setGender(DEFAULT_GENDER);
                        studentDO.setSdeptId(DEFAULT_SDEPT_ID);
                        studentDO.setName(studentName);
                        studentDAO.insert(studentDO);
                    }

                    scoreDO.setStudentId(studentDO.getId());
                    // 查询有无院系
                    SdeptDO sdeptDO = sdeptDAO.getSdeptDOByName(sdeptName);
                    if (sdeptDO == null) {
                        sdeptDO = new SdeptDO();
                        sdeptDO.setName(sdeptName);
                        sdeptDAO.insert(sdeptDO);
                    }

                    // 查询有无课程
                    CourseDO courseDO = courseDAO.getCourseDOBySdeptNameAndCourseName(sdeptName, courseName);
                    if (courseDO == null) {
                        courseDO = new CourseDO();
                        courseDO.setName(courseName);
                        courseDO.setSdeptId(sdeptDO.getId());
                        courseDAO.insert(courseDO);
                    }
                    scoreDO.setCourseId(courseDO.getId());

                    scoreDO.setScore(Double.parseDouble(score));
                    // 有无该条成绩记录
                    ScoreDO check = scoreDAO.getScoreDOByStudentIdAndCourseId(studentDO.getId(), courseDO.getId());
                    if (check == null) {
                        scoreDAO.insert(scoreDO);
                    } else {
                        check.setScore(scoreDO.getScore());
                        scoreDAO.updateScore(check);
                    }

                }

            }
            LOG.info("upload students success");
        } catch (Exception e) {
            LOG.error("import error, e={}", e);
        }

    }
}
    